package util.excel;

import com.google.common.io.Resources;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;

/**
 * Created by Sunny.xu on 1/10/2018.
 * 公共抽象excel导出Controller
 */
public abstract class AbstractExportExcel {

    private static final Logger LOGGER = LoggerFactory.getLogger(AbstractExportExcel.class);

    private Short defaultHeight = null;

    /**
     * 准备导入模版
     */
    public void write(HttpServletResponse response,Boolean newExcel) {
        response.setHeader("Content-Disposition", "attachment;filename=export.xls");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            if(!newExcel) {
                POIFSFileSystem fs = new POIFSFileSystem(Resources.asByteSource(Resources.getResource(getExcelUri())).openStream());
                HSSFWorkbook workBook = new HSSFWorkbook(fs);
                buildExcel(workBook);
                workBook.write(response.getOutputStream());
            }else{
                HSSFWorkbook workbook = buildNewExcel();
                workbook.write(response.getOutputStream());
            }

        } catch (FileNotFoundException e) {
            LOGGER.error("the model xls can not find! error:", e);
        } catch (IOException e) {
            LOGGER.error("the java IO error:", e);
        } finally {
            //poi版本高的自动释放无需才关闭
/*            if (null != workBook) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    LOGGER.error("close workbook IO error:", e);
                }
            }

            if (null != fs) {
                try {
                    fs.close();
                } catch (IOException e) {
                    LOGGER.error("close POIFSFileSystem IO error:", e);
                }
            }*/
        }
    }

    protected abstract String getExcelUri();

    protected abstract void buildExcel(HSSFWorkbook workBook);

    protected abstract HSSFWorkbook buildNewExcel();

    protected void setSheetStyle(HSSFSheet sheet, int rowIndex, int cellIndex, HSSFCellStyle style) {
        HSSFRow row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
            if (defaultHeight != null) {
                row.setHeight(defaultHeight);
            }
        }
        HSSFCell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = row.createCell(cellIndex);
        }
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    protected void setSheetValue(HSSFSheet sheet, int rowIndex, int cellIndex, String value) {
        HSSFRow row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
            if (defaultHeight != null) {
                row.setHeight(defaultHeight);
            }
        }
        HSSFCell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = row.createCell(cellIndex);
        }
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(value);
    }

    protected void setSheetValue(HSSFSheet sheet, int rowIndex, int cellIndex, String value, HSSFCellStyle style) {
        HSSFRow row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
            if (defaultHeight != null) {
                row.setHeight(defaultHeight);
            }
        }
        HSSFCell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = row.createCell(cellIndex);
        }
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    protected void setSheetValue(HSSFSheet sheet, int rowIndex, int cellIndex, double value, HSSFCellStyle style) {
        HSSFRow row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
            if (defaultHeight != null) {
                row.setHeight(defaultHeight);
            }
        }
        HSSFCell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = row.createCell(cellIndex);
        }

        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    protected void setSheetValue(HSSFSheet sheet, int rowIndex, int cellIndex, double value) {
        HSSFRow row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
            if (defaultHeight != null) {
                row.setHeight(defaultHeight);
            }
        }
        HSSFCell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = row.createCell(cellIndex);
        }
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
    }

    protected Double getSheetValue(HSSFSheet sheet, int rowIndex, int cellIndex) {
        if (sheet.getRow(rowIndex) == null) {
            return null;
        } else {
            if (sheet.getRow(rowIndex).getCell(cellIndex) == null) {
                return null;
            } else {
                return sheet.getRow(rowIndex).getCell(cellIndex).getNumericCellValue();
            }
        }
    }

    /**
     * 合并单元格
     */
    protected void mergedRegion(HSSFSheet sheet, int rowFrom, int rowTo, int columnFrom, int columnTo, HSSFCellStyle cellStyle) {
        CellRangeAddress region = new CellRangeAddress(rowFrom, rowTo, columnFrom, columnTo);
        sheet.addMergedRegion(region);
        // 设置合并单元格的样式
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                setSheetStyle(sheet, i, j, cellStyle);
            }
        }
    }

    /**
     * 合并单元格
     */
    protected void mergedRegion(HSSFSheet sheet, int rowFrom, int rowTo, int columnFrom, int columnTo) {
        sheet.addMergedRegion(new CellRangeAddress(rowFrom, rowTo, columnFrom, columnTo));
    }

    protected HSSFCellStyle getCellStyle(HSSFSheet sheet, int rowIndex, int cellIndex) {
        if (sheet.getRow(rowIndex) == null) {
            return null;
        } else {
            if (sheet.getRow(rowIndex).getCell(cellIndex) == null) {
                return null;
            } else {
                return sheet.getRow(rowIndex).getCell(cellIndex).getCellStyle();
            }
        }
    }

    public Short getDefaultHeight() {
        return defaultHeight;
    }

    public void setDefaultHeight(Short defaultHeight) {
        this.defaultHeight = defaultHeight;
    }

    /**
     * 插入图片
     */
    public static void setPicture(HSSFWorkbook wb, HSSFSheet sheet, String url, int rowFrom, int rowTo, int columnFrom, int columnTo) {
        HSSFClientAnchor anchor = new HSSFClientAnchor(100, 25, 900, 230, (short) columnFrom, rowFrom, (short) columnTo, rowTo);

        BufferedImage bufferImg = null;
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        try {
            bufferImg = ImageIO.read(new File(url));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
            int i = wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);
            sheet.createDrawingPatriarch().createPicture(anchor, i);
        } catch (Exception e) {
            LOGGER.error("load picture error:", e);
        } finally {
            try {
                byteArrayOut.close();
            } catch (IOException e) {
                LOGGER.error("close byteArrayOut IO error:", e);
            }
        }
    }

}
